package com.gxuwz.medical.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import com.gxuwz.medical.database.DbUtil;
import com.gxuwz.medical.domain.menu.Menu;
import com.gxuwz.medical.domain.role.Role;
import com.gxuwz.medical.vo.PageBean;

public class RoleDao {
	public void add(Role r) throws SQLException {
		try {
			DbUtil db = new DbUtil();
			String sql = "insert into t_role(roleid,roleName) values('"
					+ r.getRoleid() + "','" + r.getRoleName() + "')";
			db.executeUpdate(sql);
			Iterator<Menu> it = r.getMenus().iterator();
			while (it.hasNext()) {
					sql = "insert into role_menu(roleid,menuid) values('"
							+ r.getRoleid() + "','" + it.next().getMenuid() + "')";
					db.executeUpdate(sql);
			}
		} catch (SQLException e) {
			throw new SQLException("添加角色失败 " + e.getMessage(), e);
		}
	}

	public void delete(String roleid) throws SQLException {
		try {
			DbUtil db = new DbUtil();
			String sql = "delete from t_role where roleid='" + roleid + "'";
			db.executeUpdate(sql);
		} catch (SQLException e) {
			throw new SQLException("删除角色失败" + e.getMessage(), e);
		}
	}

	public Role query(String roleid) throws SQLException {
		Role r =new Role();
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select * from t_role where roleid='" + roleid
					+ "'";
			rs = db.executeQuery(stmt, sql);
			if (rs.next()) {
				r.setRoleid(rs.getString("roleid"));
				r.setRoleName(rs.getString("roleName"));
			}
			sql = "select * from role_menu where roleid='" + roleid
					+ "'";
			rs = db.executeQuery(stmt, sql);
			Set<Menu> menus = new HashSet();
			while(rs.next()){
				Menu m = new Menu();
				m=new MenuDao().query(rs.getString("menuid"));
				menus.add(m);
			}
			r.setMenus(menus);
		} catch (SQLException e) {
			throw new SQLException("查询角色失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
		return r;
	}
	public int count() throws SQLException{//返回记录数
		int count = 0;
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select count(roleid) from t_role";
			rs = stmt.executeQuery(sql);
			if(rs.next())
				count = rs.getInt(1);
		}catch (SQLException e) {
			throw new SQLException("查询记录数量失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
		return count;
	}

	public PageBean queryAll(int page,int size) throws SQLException {
		PageBean p = new PageBean(page,size,count());
		List rlist = new ArrayList();
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select * from t_role limit "+(page-1)*size+","+size+"";
			rs = db.executeQuery(stmt, sql);
			while (rs.next()) {
				Role role = new Role();
				role.setRoleid(rs.getString("roleid"));
				role.setRoleName(rs.getString("roleName"));
				rlist.add(role);
			}
			p.setPageData(rlist);
			return p;

		} catch (SQLException e) {
			throw new SQLException("查询角色失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
	}
	
	public List<Role> queryAll() throws SQLException {
		List<Role> rlist = new ArrayList<Role>();
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select * from t_role";
			rs = db.executeQuery(stmt, sql);
			while (rs.next()) {
				Role role = new Role();
				role.setRoleid(rs.getString("roleid"));
				role.setRoleName(rs.getString("roleName"));
				rlist.add(role);
			}
			return rlist;

		} catch (SQLException e) {
			throw new SQLException("查询角色失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
	}
	
	

	public void update(Role r) throws SQLException {
		try {
			DbUtil db = new DbUtil();
			String sql = "update t_role set roleName='" + r.getRoleName() + "' where roleid='" + r.getRoleid() + "'";
			db.executeUpdate(sql);
			sql="delete from role_menu where roleid='"+r.getRoleid()+"'";
			db.executeUpdate(sql);
			Iterator<Menu> it = r.getMenus().iterator();
			while (it.hasNext()) {
					sql = "insert into role_menu(roleid,menuid) values('"
							+ r.getRoleid() + "','" + it.next().getMenuid() + "')";
					db.executeUpdate(sql);
			}
		} catch (SQLException e) {
			throw new SQLException("修改角色失败 " + e.getMessage(), e);
		}
	}
}
